package com.fosu.dao;


import com.fosu.Utils.SimpleSelectInExtendedLanguageDriver;
import com.fosu.vo.MiniProduct;
import com.fosu.domain.Product;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;


import javax.servlet.http.HttpServletResponse;
import java.util.List;

/**
 * 商品持久层
 *
 * @author manno
 */
@Mapper
@Repository
public interface ProductDao {
    /**
     * 分页获取最新商品
     * @param currentPage 当前页数
     * @param pageSize 页大小
     * @return List<MiniProduct> 精简商品信息集合
     */
    @Select("SELECT p.product_id,u.user_id,u.nick_name,u.avatar_url" +
            ",p.price,p.last_modified_date,p.pageviews" +
            ",p.title,p.customize_categories,p.off_shelf " +
            ",p.purchase_price " +
            "FROM product p,user u " +
            "where p.user_id = u.user_id " +
            "and p.off_shelf = 0 " +
            "ORDER BY p.last_modified_date " +
            "DESC Limit #{currentPage},#{pageSize}")
    @Results(id = "miniProductMap", value = {
            @Result(id = true, property = "productId", column = "product_id"),
            @Result(property = "productPrice", column = "price"),
            @Result(property = "lastModifiedDate", column = "last_modified_date"),
            @Result(property = "pageViews", column = "pageviews"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "nickName", column = "nick_name"),
            @Result(property = "avatarUrl", column = "avatar_url"),
            @Result(property = "productTitle", column = "title"),
            @Result(property = "purchasePrice", column = "purchase_price"),
            @Result(property = "productCategories"
                    , column = "product_id"
                    , javaType = java.util.List.class
                    , many = @Many(select = "com.fosu.dao.CategoriesDao.findCategoriesById")),
            @Result(property = "rawCustomizeCategories", column = "customize_categories"),
            @Result(property = "productImgs"
                    , column = "product_id"
                    , javaType = java.util.List.class
                    , many = @Many(select = "com.fosu.dao.ProductImgDao.findImgsById")),
            @Result(property = "compressImgs"
                    , column = "product_id"
                    , javaType = java.util.List.class
                    , many = @Many(select = "com.fosu.dao.CompressImgDao.findImgsById")),
            @Result(property = "productLabels"
                    , column = "product_id"
                    , one = @One(select = "com.fosu.dao.LabelDao.findLabelById")),
            @Result(property = "productCampus"
                    , column = "product_id"
                    , many = @Many(select = "com.fosu.dao.CampusDao.findCampusById")),
            @Result(property = "offShelf",column = "off_shelf")
    })
    List<MiniProduct> getProducts(@Param("currentPage") int currentPage,@Param("pageSize") int pageSize);


    /**
     * 根据用户id获取到我想要的商品
     * @param userId
     * @return
     */
    @ResultMap(value = "miniProductMap")
    @Select("SELECT p.product_id,u.user_id,u.nick_name,u.avatar_url" +
            ",p.price,p.last_modified_date,p.pageviews" +
            ",p.title,p.customize_categories,p.off_shelf " +
            "FROM product p,user u " +
            "where p.user_id = u.user_id " +
            "and p.off_shelf = 0 "+
            "and product_id in (select product_id from favorite_product where user_id =#{userId}) " +
            "limit #{currPage},#{pageSize}")
    List<MiniProduct> getFavoriteProductByUserId(@Param("userId")int userId,@Param("currPage")Integer currPage,@Param("pageSize") Integer pageSize);

    /**
     * 获取我想要表的数量
     * @return
     */
    @Select("select count(*) from favorite_product where user_id =#{userId}")
    Integer getFavoriteProductCount(@Param("userId")int userId);


    /**
     * 根据首页用户传入的商品id获得特定商品信息
     * @param productId 商品id
     * @return Product 详细商品信息类对象
     */
    @Select("SELECT * " +
            "FROM product  " +
            "WHERE product_id = #{productId} ")
    @Results(id = "productMap", value = {
            /*商品表*/
            @Result(id = true, property = "productId", column = "product_id"),
            @Result(property = "productPrice", column = "price"),
            @Result(property = "productDetail", column = "detail"),
            @Result(property = "lastModifiedDate", column = "last_modified_date"),
            @Result(property = "pageViews", column = "pageviews"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "productTitle", column = "title"),
            @Result(property = "purchasePrice", column = "purchase_price"),
            @Result(property = "user"
                    , column = "user_id"
                    , one = @One(select = "com.fosu.dao.UserDao.findUserById")),
            @Result(property = "productCampus"
                    , column = "product_id"
                    , many = @Many(select = "com.fosu.dao.CampusDao.findCampusById")),
            @Result(property = "productImgs"
                    , column = "product_id"
                    , javaType = java.util.List.class
                    , many = @Many(select = "com.fosu.dao.ProductImgDao.findImgsById")),
            @Result(property = "compressImgs"
                    , column = "product_id"
                    , javaType = java.util.List.class
                    , many = @Many(select = "com.fosu.dao.CompressImgDao.findImgsById")),
            @Result(property = "productLabels"
                    , column = "product_id"
                    , one = @One(select = "com.fosu.dao.LabelDao.findLabelById")),
            @Result(property = "productCategories"
                    , column = "product_id"
                    , javaType = java.util.List.class
                    , many = @Many(select = "com.fosu.dao.CategoriesDao.findCategoriesById")),
            @Result(property = "rawCustomizeCategories", column = "customize_categories"),
    })
    Product findProductById(@Param("productId") int productId);


    /**
     * 根据id区间查询商品
     * @param productIds 商品id区间
     * @return List<MiniProduct> 详细商品信息集合
     */
    @Lang(SimpleSelectInExtendedLanguageDriver.class)
    @Select("SELECT DISTINCT p.product_id,u.user_id,u.nick_name,u.avatar_url" +
            ",p.price,p.last_modified_date,p.pageviews,p.off_shelf" +
            ",p.title,p.customize_categories " +
            ",p.purchase_price " +
            "FROM product p,user u " +
            "WHERE product_id in (#{productIds}) " +
            "and p.user_id = u.user_id " +
            "order by last_modified_date desc")
    @ResultMap(value = "miniProductMap")
    List<MiniProduct> getProductsByIds(@Param("productIds") List<Integer> productIds);


    /**
     * 根据勾选的校区和分类查询商品
     * @param currentPage 当前页数
     * @param pageSize 页大小
     * @param campusId 校区id集合
     * @param categoriesId 分类id集合
     * @return List<MiniProduct> 精简商品信息集合
     */
    @Lang(SimpleSelectInExtendedLanguageDriver.class)
    @Select("SELECT DISTINCT p.product_id,u.user_id,u.nick_name,u.avatar_url" +
            ",p.price,p.last_modified_date,p.pageviews" +
            ",p.title,p.customize_categories " +
            ",p.purchase_price " +
            "FROM product p,user u,product_categories pc,categories c,product_campus pcam,campus cam " +
            "WHERE and p.off_shelf = 0 AND " +
            "(p.product_id = pcam.product_id " +
            "and p.user_id = u.user_id " +
            "and pcam.campus_id = cam.campus_id " +
            "and pcam.campus_id in (#{campusId}) " +
            ") " +
            "AND (p.product_id = pc.product_id " +
            "and pc.categories_id = c.categories_id " +
            "and pc.categories_id in (#{categoriesId})) " +
            "ORDER BY last_modified_date DESC Limit #{currentPage},#{pageSize}")
    @ResultMap(value = "miniProductMap")
    List<MiniProduct> getProductsByCampusAndCategories
    (@Param("currentPage") int currentPage
            , @Param("pageSize") int pageSize
            , @Param("campusId") List<String> campusId
            , @Param("categoriesId") List<String> categoriesId);


    /**
     * 插入product
     * @param product 商品对象
     */
    @Insert("insert into product(title,price,detail,customize_categories,user_id,purchase_price) " +
            "values(#{productTitle},#{productPrice},#{productDetail},#{rawCustomizeCategories},#{userId},#{purchasePrice})")
    @Results(id = "productMap", value = {
            @Result(id = true, property = "productId", column = "product_id"),
            @Result(property = "productTitle", column = "title"),
            @Result(property = "productPrice", column = "price"),
            @Result(property = "productDetail", column = "detail"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "purchasePrice", column = "purchase_price"),
            @Result(property = "rawCustomizeCategories", column = "customize_categories"),
    })
    @Options(useGeneratedKeys = true, keyProperty = "productId", keyColumn = "product_id")
    void saveProduct(Product product);

    /**
     * 更新product
     * @param product 商品对象
     */
    @Insert("update product " +
            "set title=#{productTitle}," +
            "price=#{productPrice}," +
            "purchase_price=#{purchasePrice}," +
            "detail=#{productDetail}," +
            "customize_categories=#{rawCustomizeCategories} " +
            "WHERE product.product_id = #{productId}" )
//    @Results(id = "updateMap", value = {
//            @Result(property = "productTitle", column = "title"),
//            @Result(property = "productPrice", column = "price"),
//            @Result(property = "productDetail", column = "detail"),
//            @Result(property = "userId", column = "user_id"),
//            @Result(property = "rawCustomizeCategories", column = "customize_categories"),
//    })
    @ResultMap("productMap")
    void updateProduct(Product product);



    /**根据信息查找product数据——按用户输入的字串匹配度排序
     * 默认查询最多5条
     * @param searchMsg 用户输入的搜索字串
     * @return List<MiniProduct> 精简商品信息集合
     */
    @ResultMap(value = "miniProductMap")
        @Select("SELECT DISTINCT p.product_id,u.user_id,u.nick_name,u.avatar_url " +
                " ,p.price,p.last_modified_date,p.pageviews " +
                " ,p.title,p.customize_categories  " +
                " ,p.purchase_price " +
                " FROM product p,user u,product_categories pc,categories c,product_campus pcam,campus cam " +
                " where match(title) against( concat('%',#{searchMsg},'%') ) " +
                " and p.off_shelf = 0 " +
                " and p.user_id = u.user_id ORDER BY last_modified_date DESC Limit #{currPage},#{pageSize}")
    List<MiniProduct> findMsgBySearch(@Param("searchMsg")String searchMsg
            ,@Param("currPage")Integer currPage
            ,@Param("pageSize")Integer pageSize);

    /**根据信息查找product数据——按分类与用户输入的字串匹配度排序
     * @param searchMsg 用户输入的搜索字串
     *                  默认查询最多5条
     * @return List<MiniProduct> 精简商品信息集合
     */
    @ResultMap(value = "miniProductMap")
    @Select("SELECT DISTINCT p.product_id,u.user_id,u.nick_name,u.avatar_url " +
            " ,p.price,p.last_modified_date,p.pageviews " +
            " ,p.title,p.customize_categories  " +
            ",p.purchase_price " +
            " FROM product p,user u,product_categories pc,categories c,product_campus pcam,campus cam " +
            "where p.user_id=u.user_id " +
            "and p.off_shelf = 0 " +
            "and p.product_id " +
            "in" +
            "(select product_id from product_categories where categories_id in" +
            "(select categories_id from categories where match(categories_name) against( concat('%',#{searchMsg},'%')) ))  " +
            "ORDER BY last_modified_date DESC Limit #{currPage},#{pageSize}")
    List<MiniProduct> findMsgByCategories(@Param("searchMsg")String searchMsg
            ,@Param("currPage")Integer currPage
            ,@Param("pageSize")Integer pageSize);

    /**根据信息查找product数据——按校区与用户输入的字串匹配度排序
     * @param searchMsg 用户输入的搜索字串
     *                  默认查询最多5条
     * @return List<MiniProduct> 精简商品信息集合
     */
    @ResultMap(value = "miniProductMap")
    @Select("SELECT DISTINCT p.product_id,u.user_id,u.nick_name,u.avatar_url " +
            " ,p.price,p.last_modified_date,p.pageviews " +
            " ,p.title,p.customize_categories  " +
            ",p.purchase_price " +
            " FROM product p,user u,product_categories pc,categories c,product_campus pcam,campus cam " +
            "where p.user_id=u.user_id " +
            "and p.off_shelf = 0 " +
            "and p.product_id " +
            "in  " +
            "(select product_id from product_campus where campus_id in" +
            "(select campus_id from campus where match(campus_name) against( concat('%',#{searchMsg},'%')) )) " +
            " ORDER BY last_modified_date DESC Limit #{currPage},#{pageSize}")
    List<MiniProduct> findMsgByCampus(@Param("searchMsg")String searchMsg
            ,@Param("currPage")Integer currPage
            ,@Param("pageSize")Integer pageSize);


    /**根据信息查找product数据——按自定义分类与用户输入的字串匹配度排序
     * @param searchInfo 自定义分类字串
     *                   默认查询最多5条
     * @return List<MiniProduct> 精简商品信息集合
     */
    @ResultMap(value = "miniProductMap")
    @Select("SELECT DISTINCT p.product_id,u.user_id,u.nick_name,u.avatar_url " +
            " ,p.price,p.last_modified_date,p.pageviews " +
            " ,p.title,p.customize_categories  " +
            ",p.purchase_price " +
            " FROM product p,user u,product_categories pc,categories c,product_campus pcam,campus cam " +
            " where  match(customize_categories) against( concat('%',#{searchInfo},'%'))  " +
            "and p.user_id = u.user_id " +
            "and p.off_shelf = 0 ORDER BY last_modified_date DESC Limit #{currPage},#{pageSize}")
    List<MiniProduct> findMsgByCustomizeCategories(@Param("searchInfo")String searchInfo
    ,@Param("currPage")Integer currPage
    ,@Param("pageSize")Integer pageSize);

    /**
     * 查找是否已收藏商品
     * @param userId 用户id
     * @param productId 商品id
     * @return Integer 返回1说明已收藏,0说明未收藏
     * */
    @Select("SELECT COUNT(*) FROM favorite_product " +
            "WHERE user_id=#{userId} and product_id=#{productId}")
    Integer findIsFavorite(@Param("userId")Integer userId,@Param("productId") Integer productId);

    /**移除收藏
     * @param userId 用户id
     * @param productId 商品id
     * */
    @Delete("DELETE FROM favorite_product " +
            "WHERE user_id=#{userId} and product_id=#{productId}")
    void removeFavorite(@Param("userId")Integer userId,@Param("productId") Integer productId);

    /**添加收藏
     * @param userId 用户id
     * @param productId 商品id
     * */
    @Delete("INSERT INTO favorite_product(user_id,product_id) " +
            "VALUES(#{userId},#{productId})")
    void addFavorite(@Param("userId")Integer userId
            ,@Param("productId") Integer productId);

    /**获得用户发布的商品id集合
     * @param userId 用户id
     * @return List<Integer> 用户发布商品的id集合
     */
    @Select("SELECT DISTINCT p.product_id " +
            "FROM product p "+
            "WHERE p.user_id = #{userId} "+
            "order by product_id desc Limit #{currentPage},#{pageSize} ")
    List<Integer> userReleased(@Param("userId") Integer userId
            ,@Param("currentPage") int currentPage
            , @Param("pageSize") int pageSize);


    /**查看商品今日内是否已被浏览
     * @param userId 当前正在浏览的用户id
     * @param productId 被浏览的商品id
     * @return 1：已被浏览,0：未被浏览
     */
    @Select("select count(*) from is_viewed where user_id=#{userId} and product_id=#{productId}")
    Integer isViewed(@Param("userId") Integer userId
            ,@Param("productId") Integer productId);

    /**添加商品被浏览数
     * @param productId 被浏览的商品id
     */
    @Select("update product set pageviews=pageviews+1 where product_id = #{productId}")
    void addViewed(@Param("productId") Integer productId);

    /**添加商品被浏览数
     * @param userId 当前正在浏览的用户id
     * @param productId 被浏览的商品id
     */
    @Select("insert is_viewed(user_id,product_id) values(#{userId},#{productId})")
    void updateIsViewed(@Param("userId") Integer userId
            ,@Param("productId") Integer productId);

    /**下架商品
     * @param productId 要下架商品的id
     * @return Product 被下架商品的信息
     */
    @Update("update product set off_shelf=1 where product_id=#{productId}")
    Product offShelfProduct(@Param("productId")Integer productId);
}




